import sqlite3
import os
import tkinter as tk
from tkinter import ttk, filedialog, messagebox
import re

class DatabaseManager:
    def __init__(self, db_path=None):
        self.db_path = db_path
        self.conn = None
        self.cursor = None
        
    def connect(self, db_path=None):
        """连接到数据库"""
        if db_path:
            self.db_path = db_path
        
        if not self.db_path:
            return False, "未指定数据库路径"
        
        if not os.path.exists(self.db_path):
            return False, f"数据库文件 '{self.db_path}' 不存在"
        
        try:
            self.conn = sqlite3.connect(self.db_path)
            self.cursor = self.conn.cursor()
            return True, "成功连接到数据库"
        except sqlite3.Error as e:
            return False, f"连接数据库时出错: {e}"
    
    def close(self):
        """关闭数据库连接"""
        if self.conn:
            self.conn.close()
            self.conn = None
            self.cursor = None
    
    def get_tables(self):
        """获取数据库中的所有表"""
        if not self.conn:
            return []
        
        try:
            self.cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
            return [row[0] for row in self.cursor.fetchall()]
        except sqlite3.Error:
            return []
    
    def get_table_columns(self, table_name):
        """获取表的列信息"""
        if not self.conn:
            return []
        
        try:
            self.cursor.execute(f"PRAGMA table_info({table_name})")
            return [(row[0], row[1], row[2], row[3], row[5]) for row in self.cursor.fetchall()]
        except sqlite3.Error:
            return []
    
    def add_column(self, table_name, column_name, column_type):
        """添加列到表"""
        if not self.conn:
            return False, "未连接到数据库"
        
        try:
            # 检查列是否已存在
            columns = self.get_table_columns(table_name)
            for col in columns:
                if col[1] == column_name:
                    return False, f"列 '{column_name}' 已存在"
            
            # 添加列
            sql = f"ALTER TABLE {table_name} ADD COLUMN {column_name} {column_type}"
            self.cursor.execute(sql)
            self.conn.commit()
            return True, f"已成功添加列 '{column_name}'"
        except sqlite3.Error as e:
            return False, f"添加列时出错: {e}"
    
    def drop_column(self, table_name, column_name):
        """删除表中的列（SQLite不直接支持，需要创建新表）"""
        if not self.conn:
            return False, "未连接到数据库"
        
        try:
            # 获取表的所有列
            self.cursor.execute(f"PRAGMA table_info({table_name})")
            columns = self.cursor.fetchall()
            
            # 检查列是否存在
            column_exists = False
            for col in columns:
                if col[1] == column_name:
                    column_exists = True
                    break
            
            if not column_exists:
                return False, f"列 '{column_name}' 不存在"
            
            # 获取除要删除列外的所有列
            remaining_columns = [col[1] for col in columns if col[1] != column_name]
            columns_str = ", ".join(remaining_columns)
            
            # 创建新表
            temp_table = f"{table_name}_temp"
            self.cursor.execute(f"CREATE TABLE {temp_table} AS SELECT {columns_str} FROM {table_name}")
            
            # 删除原表并重命名新表
            self.cursor.execute(f"DROP TABLE {table_name}")
            self.cursor.execute(f"ALTER TABLE {temp_table} RENAME TO {table_name}")
            
            self.conn.commit()
            return True, f"已成功删除列 '{column_name}'"
        except sqlite3.Error as e:
            return False, f"删除列时出错: {e}"
    
    def fix_todos_table(self):
        """修复todos表，添加缺少的列"""
        if not self.conn:
            return False, "未连接到数据库"
        
        # 检查todos表是否存在
        self.cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='todos'")
        if not self.cursor.fetchone():
            return False, "'todos'表不存在"
        
        # 需要检查的列及其数据类型
        required_columns = {
            'id': 'INTEGER PRIMARY KEY',
            'content': 'TEXT',
            'datetime': 'TEXT',
            'completed': 'INTEGER',
            'important': 'INTEGER',
            'completion_datetime': 'TEXT',
            'due_date': 'TEXT',
            'reminder_time': 'TEXT',
            'is_overdue': 'INTEGER'
        }
        
        # 获取todos表的当前列信息
        self.cursor.execute("PRAGMA table_info(todos)")
        existing_columns = {row[1]: row[2] for row in self.cursor.fetchall()}
        
        # 检查缺少的列并添加
        missing_columns = []
        added_columns = []
        errors = []
        
        for col_name, col_type in required_columns.items():
            if col_name not in existing_columns:
                missing_columns.append((col_name, col_type))
        
        # 如果有缺少的列，添加它们
        if missing_columns:
            for col_name, col_type in missing_columns:
                # SQLite不允许在ALTER TABLE语句中使用PRIMARY KEY约束
                if col_name == 'id' and col_type == 'INTEGER PRIMARY KEY':
                    # 检查是否有其他主键
                    has_primary_key = False
                    for _, existing_type in existing_columns.items():
                        if 'PRIMARY KEY' in existing_type.upper():
                            has_primary_key = True
                            break
                    
                    if has_primary_key:
                        # 如果已有主键，只添加普通INTEGER列
                        col_type = 'INTEGER'
                    else:
                        errors.append(f"无法添加主键列'id'，可能需要手动重建表")
                        continue
                
                # 添加列
                try:
                    sql = f"ALTER TABLE todos ADD COLUMN {col_name} {col_type}"
                    self.cursor.execute(sql)
                    added_columns.append(f"{col_name} ({col_type})")
                except sqlite3.Error as e:
                    errors.append(f"添加列 {col_name} 时出错: {e}")
            
            self.conn.commit()
            
            if added_columns:
                result_message = f"已添加 {len(added_columns)} 个列: " + ", ".join(added_columns)
                if errors:
                    result_message += "\n\n错误: " + "\n".join(errors)
                return True, result_message
            else:
                return False, "未能添加任何列:\n" + "\n".join(errors)
        else:
            return True, "todos表已包含所有必要的列"


class DatabaseManagerApp(tk.Tk):
    def __init__(self):
        super().__init__()
        
        self.title("数据库管理器")
        self.geometry("800x600")
        self.minsize(800, 600)
        
        self.db_manager = DatabaseManager()
        self.current_table = None
        
        self.create_widgets()
        self.center_window()
    
    def center_window(self):
        """将窗口居中显示"""
        self.update_idletasks()
        width = self.winfo_width()
        height = self.winfo_height()
        x = (self.winfo_screenwidth() // 2) - (width // 2)
        y = (self.winfo_screenheight() // 2) - (height // 2)
        self.geometry(f'{width}x{height}+{x}+{y}')
    
    def create_widgets(self):
        """创建GUI组件"""
        # 创建主框架
        main_frame = ttk.Frame(self)
        main_frame.pack(fill=tk.BOTH, expand=True, padx=10, pady=10)
        
        # 创建顶部框架
        top_frame = ttk.Frame(main_frame)
        top_frame.pack(fill=tk.X, pady=(0, 10))
        
        # 数据库路径显示和选择按钮
        ttk.Label(top_frame, text="数据库:").pack(side=tk.LEFT, padx=(0, 5))
        self.db_path_var = tk.StringVar()
        ttk.Entry(top_frame, textvariable=self.db_path_var, width=50, state="readonly").pack(side=tk.LEFT, fill=tk.X, expand=True, padx=(0, 5))
        ttk.Button(top_frame, text="选择数据库", command=self.select_database).pack(side=tk.LEFT)
        
        # 创建中间框架
        middle_frame = ttk.Frame(main_frame)
        middle_frame.pack(fill=tk.BOTH, expand=True, pady=(0, 10))
        
        # 创建左侧表格列表框架
        left_frame = ttk.LabelFrame(middle_frame, text="表格")
        left_frame.pack(side=tk.LEFT, fill=tk.Y, padx=(0, 5))
        
        # 表格列表
        self.tables_listbox = tk.Listbox(left_frame, width=20)
        self.tables_listbox.pack(fill=tk.BOTH, expand=True)
        self.tables_listbox.bind("<<ListboxSelect>>", self.on_table_select)
        
        # 创建右侧列信息框架
        right_frame = ttk.LabelFrame(middle_frame, text="列信息")
        right_frame.pack(side=tk.LEFT, fill=tk.BOTH, expand=True)
        
        # 列信息表格
        columns = ("序号", "列名", "类型", "非空", "主键")
        self.columns_tree = ttk.Treeview(right_frame, columns=columns, show="headings")
        
        # 设置列标题
        for col in columns:
            self.columns_tree.heading(col, text=col)
            self.columns_tree.column(col, width=80)
        
        # 添加滚动条
        scrollbar = ttk.Scrollbar(right_frame, orient=tk.VERTICAL, command=self.columns_tree.yview)
        self.columns_tree.configure(yscrollcommand=scrollbar.set)
        
        self.columns_tree.pack(side=tk.LEFT, fill=tk.BOTH, expand=True)
        scrollbar.pack(side=tk.RIGHT, fill=tk.Y)
        
        # 创建底部框架
        bottom_frame = ttk.Frame(main_frame)
        bottom_frame.pack(fill=tk.X)
        
        # 添加列框架
        add_frame = ttk.LabelFrame(bottom_frame, text="添加列")
        add_frame.pack(side=tk.LEFT, fill=tk.X, expand=True, padx=(0, 5))
        
        ttk.Label(add_frame, text="列名:").grid(row=0, column=0, padx=5, pady=5, sticky=tk.W)
        self.add_column_name = ttk.Entry(add_frame, width=20)
        self.add_column_name.grid(row=0, column=1, padx=5, pady=5, sticky=tk.W)
        
        ttk.Label(add_frame, text="类型:").grid(row=0, column=2, padx=5, pady=5, sticky=tk.W)
        self.add_column_type = ttk.Combobox(add_frame, width=15, values=["INTEGER", "TEXT", "REAL", "BLOB", "INTEGER PRIMARY KEY", "TEXT NOT NULL", "INTEGER NOT NULL"])
        self.add_column_type.grid(row=0, column=3, padx=5, pady=5, sticky=tk.W)
        
        ttk.Button(add_frame, text="添加列", command=self.add_column).grid(row=0, column=4, padx=5, pady=5)
        
        # 删除列框架
        delete_frame = ttk.LabelFrame(bottom_frame, text="删除列")
        delete_frame.pack(side=tk.LEFT, fill=tk.X, expand=True)
        
        ttk.Label(delete_frame, text="列名:").grid(row=0, column=0, padx=5, pady=5, sticky=tk.W)
        self.delete_column_name = ttk.Combobox(delete_frame, width=20)
        self.delete_column_name.grid(row=0, column=1, padx=5, pady=5, sticky=tk.W)
        
        ttk.Button(delete_frame, text="删除列", command=self.delete_column).grid(row=0, column=2, padx=5, pady=5)
        
        # 修复todos表按钮
        fix_frame = ttk.Frame(main_frame)
        fix_frame.pack(fill=tk.X, pady=(10, 0))
        
        ttk.Button(fix_frame, text="一键修复todos表", command=self.fix_todos_table).pack(side=tk.LEFT)
        ttk.Label(fix_frame, text="(添加缺少的列: id, content, datetime, completed, important, completion_datetime, due_date, reminder_time, is_overdue)").pack(side=tk.LEFT, padx=(5, 0))
    
    def select_database(self):
        """选择数据库文件"""
        db_path = filedialog.askopenfilename(
            title="选择SQLite数据库文件",
            filetypes=[("SQLite数据库", "*.db *.sqlite *.sqlite3"), ("所有文件", "*.*")]
        )
        
        if db_path:
            success, message = self.db_manager.connect(db_path)
            if success:
                self.db_path_var.set(db_path)
                self.refresh_tables()
                messagebox.showinfo("连接成功", message)
            else:
                messagebox.showerror("连接失败", message)
    
    def refresh_tables(self):
        """刷新表格列表"""
        self.tables_listbox.delete(0, tk.END)
        tables = self.db_manager.get_tables()
        for table in tables:
            self.tables_listbox.insert(tk.END, table)
    
    def on_table_select(self, event):
        """当选择表格时更新列信息"""
        selection = self.tables_listbox.curselection()
        if selection:
            table_name = self.tables_listbox.get(selection[0])
            self.current_table = table_name
            self.refresh_columns()
    
    def refresh_columns(self):
        """刷新列信息"""
        # 清空表格
        for item in self.columns_tree.get_children():
            self.columns_tree.delete(item)
        
        # 清空删除列下拉框
        self.delete_column_name['values'] = []
        
        if not self.current_table:
            return
        
        # 获取列信息
        columns = self.db_manager.get_table_columns(self.current_table)
        column_names = []
        
        # 填充表格
        for col in columns:
            cid, name, type_, not_null, pk = col
            self.columns_tree.insert("", tk.END, values=(cid, name, type_, "是" if not_null else "否", "是" if pk else "否"))
            column_names.append(name)
        
        # 更新删除列下拉框
        self.delete_column_name['values'] = column_names
    
    def add_column(self):
        """添加列"""
        if not self.current_table:
            messagebox.showerror("错误", "请先选择一个表")
            return
        
        column_name = self.add_column_name.get().strip()
        column_type = self.add_column_type.get().strip()
        
        if not column_name:
            messagebox.showerror("错误", "请输入列名")
            return
        
        if not column_type:
            messagebox.showerror("错误", "请选择或输入列类型")
            return
        
        # 验证列名格式
        if not re.match(r'^[a-zA-Z_][a-zA-Z0-9_]*$', column_name):
            messagebox.showerror("错误", "列名格式无效，只能包含字母、数字和下划线，且不能以数字开头")
            return
        
        success, message = self.db_manager.add_column(self.current_table, column_name, column_type)
        if success:
            messagebox.showinfo("成功", message)
            self.refresh_columns()
            self.add_column_name.delete(0, tk.END)
        else:
            messagebox.showerror("错误", message)
    
    def delete_column(self):
        """删除列"""
        if not self.current_table:
            messagebox.showerror("错误", "请先选择一个表")
            return
        
        column_name = self.delete_column_name.get()
        if not column_name:
            messagebox.showerror("错误", "请选择要删除的列")
            return
        
        # 确认删除
        if not messagebox.askyesno("确认", f"确定要删除列 '{column_name}' 吗？此操作不可撤销。"):
            return
        
        success, message = self.db_manager.drop_column(self.current_table, column_name)
        if success:
            messagebox.showinfo("成功", message)
            self.refresh_columns()
        else:
            messagebox.showerror("错误", message)
    
    def fix_todos_table(self):
        """修复todos表"""
        if not self.db_manager.conn:
            messagebox.showerror("错误", "请先连接到数据库")
            return
        
        success, message = self.db_manager.fix_todos_table()
        if success:
            messagebox.showinfo("成功", message)
            # 如果当前显示的是todos表，刷新列信息
            if self.current_table == "todos":
                self.refresh_columns()
        else:
            messagebox.showerror("错误", message)


if __name__ == "__main__":
    app = DatabaseManagerApp()
    app.mainloop()